#!/usr/bin/python3
# coding: utf-8

import pymysql
import xlrd

import pypinyin


def insertSong(id, readable_id, name, singer_name, cover, singer_id, duration, name_pinyin, name_pinyin_header,
               tag_names, tag_ids):
    # SQL 插入语句
    sql = """INSERT INTO mm_song_for_gogo(\
    id_,readable_id, name, singer_name, cover,singer_id,duration,name_pinyin,name_pinyin_header,tag_names,tag_ids,create_by,create_time,update_by,update_time)
                 VALUES (%d, '%s', '%s','%s', '%s', '%s','%s','%s','%s','%s','%s',1,'2018-03-31 13:00:00',1,'2018-03-31 13:00:00')""" % (
        id, readable_id, name, singer_name, cover, singer_id, duration, name_pinyin, name_pinyin_header, tag_names,
        tag_ids)

    print(sql)

    try:
        cursor.execute(sql)
        db.commit()
        return cursor.lastrowid
    except Exception as e:
        print(e)
        db.rollback()

    return 0


def loadDataFromExcel(xlsfile="", maskReadableIds=[]):
    book = xlrd.open_workbook(xlsfile)  # 得到Excel文件的book对象，实例化对象
    sheet0 = book.sheet_by_index(7)  # 通过sheet索引获得sheet对象
    print("1、", sheet0)
    sheet_name = book.sheet_names()[7]  # 获得指定索引的sheet表名字
    print("2、", sheet_name)
    sheet1 = book.sheet_by_name(sheet_name)  # 通过sheet名字来获取，当然如果知道sheet名字就可以直接指定
    nrows = sheet0.nrows  # 获取行总数
    print("3、", nrows)
    # 循环打印每一行的内容
    rows = []

    for i in range(1, nrows):  # 去掉第1行标题行
        try:
            r = sheet0.row_values(i)
            rr = int(r[1])

            if rr in maskReadableIds:
                rows.append(r)

        except Exception as e:
            print(e)

    ncols = sheet0.ncols  # 获取列总数
    print("4、", ncols)
    row_data = sheet0.row_values(0)  # 获得第1行的数据列表
    print(row_data)
    col_data = sheet0.col_values(0)  # 获得第1列的数据列表
    print("5、", col_data)
    # 通过坐标读取表格中的数据
    cell_value1 = sheet0.cell_value(0, 0)
    print("6、", cell_value1)
    cell_value2 = sheet0.cell_value(0, 1)
    print("7、", cell_value2)
    return rows


def loadIds():
    f = open(r"C:\Users\Wei\Documents\WeChat Files\idevdreamob\Files\index.log")
    lines = f.readlines()
    f.close()

    ids = []
    for i in range(0, len(lines)):
        ids.append(int(lines[i].replace("\n", '')))
    return ids


def findSingerByName(singerName=''):
    sql = "SELECT * FROM mm_singer WHERE name=\"%s\"" % singerName

    print(sql)
    cursor.execute(sql)

    row = cursor.fetchone()
    return row


def selectLastSingerId():
    sql = "SELECT id_ FROM mm_singer order by id_ DESC"

    cursor.execute(sql)

    row = cursor.fetchone()
    return row[0]


def insertSinger(id=20180331, singerName='', avatar='', name_pinyin='', name_pinyin_header=''):
    sql = "INSERT INTO mm_singer(id_,name,avatar,name_pinyin,name_pinyin_header,create_by,create_time,update_by,update_time) values(%s,'%s','%s','%s','%s',1,'%s',1,'%s')" % (id, singerName, avatar, name_pinyin, name_pinyin_header, '2018-03-31 15:39:00', '2018-03-31 15:39:00')

    print(sql)

    try:
        cursor.execute(sql)
        db.commit()
        return cursor.lastrowid
    except Exception as e:
        print(e)
        db.rollback()
    return 0


def findSingerOrInsertIfNotExists(singerName):
    singerRecord = findSingerByName(singerName)
    if singerRecord is not None:
        print(singerRecord[0])
        singerRecordId = singerRecord[0]
    else:
        singerName = singerName
        avatar = 'http://vodm32lwcer.vod.126.net/vodm32lwcer/da32b33c-d275-445f-8fa4-89f8295362f1.mp4'

        try:
            name_pinyin = ''.join(pypinyin.lazy_pinyin(singerName))
        except:
            name_pinyin = singer_name

        try:
            ds = pypinyin.pinyin(singerName, style=pypinyin.FIRST_LETTER)
            s = ""
            for i in range(0, len(ds)):
                s += ds[i][0]
        except:
            s = '%s' % singer_name

        name_pinyin_header = s.upper()

        singerRecordId = insertSinger(selectLastSingerId() + 1, singerName, avatar, name_pinyin, name_pinyin_header)

    return singerRecordId


if __name__ == '__main__':
    global db, cursor
    # 打开数据库连接
    db = pymysql.connect(host="localhost", user="root", passwd="root", db="MIMI", charset="utf8")

    cursor = db.cursor()

    maskIds = loadIds()
    print(maskIds)

    xlsfile = r"E:\云端总歌单.xlsx"

    rows = loadDataFromExcel(xlsfile, maskIds)

    print(rows[0])
    print(len(rows))

    successedCount = 0

    for i in range(0, rows.__len__()):
        row = rows[i]
        id = int("20180331%d" % i)
        readableId = int(row[1])
        name = row[2]
        singer_name = row[4]

        cover = "gogo"
        singer_id = findSingerOrInsertIfNotExists(singer_name)
        duration = 100
        try:
            name_pinyin = ''.join(pypinyin.lazy_pinyin(name))
        except Exception as e:
            name_pinyin = '歌曲名有问题: %s' % name

        name_pinyin_header = row[3]
        tag_names = ''
        tag_ids = ''
        print("正在导入 [ %d ], 剩余: [ %d ]\n" % (i + 1, len(rows) - i))

        rowId = insertSong(id, readableId, name, singer_name, cover, singer_id, duration, name_pinyin, name_pinyin_header, tag_names, tag_ids)
        print(rowId)
        if rowId != 0:
            successedCount += 1

    print("总数量: %d, 已导入成功 %d" % (len(rows), successedCount))

    db.close()
